package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.QuestionDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.entity.IQuestion;
import com.googlecode.bip.entity.Question;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLQuestionDAO extends QuestionDAO
{
    private static final long serialVersionUID = 1L;
    private static final transient Logger logger = Logger.getLogger( MySQLTopicDAO.class );

    @Override //Listable<IQuestion>
    public List<IQuestion> getList( String sqlQuery ) throws SQLException, NamingException
    {
        Connection connection = MySQLDAOFactory.getConnection();

        try {
            return DBHelper.getList( this, connection, sqlQuery );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

   @Override
   public IQuestion findQuestion( int idquestion, String idlocal )
       throws NamingException, SQLException
   {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + " AND `idlocal`='"
           + idlocal
           +"';";

       if( logger.isDebugEnabled() ) {
           logger.debug( "findQuestion: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           ResultSet   rSet  = query.executeQuery( sql );
           boolean     found = rSet.first();

           if( found ) {
               return newInstance( rSet );
               }
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }

       return null;
   }

    @Override
    public List<IQuestion> findQuestions( int idquestion )
        throws SQLException, NamingException
    {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + ";";

       return getList( sql );
    }

    @Override
    public List<IQuestion> findQuestions( int idtopic, String idlocal )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `idtopic`='"
            + idtopic
            + "' AND `idlocal`='"
            + idlocal
            + "';";

        return getList( sql );
    }

    /**
     * List of idquestion for a specified topic
     * @param idtopic Topic id
     * @return a List of IQuestion
     * @throws SQLException if any
     * @throws NamingException if any
     */
    @Override
    public List<Integer> _tmp_findQuestions( int idtopic )
       throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `idtopic`='"
            + idtopic
            + "';";
        Connection connection = MySQLDAOFactory.getConnection();

        try {
            return DBHelper.getListOfInteger( "idquestion", connection, sql );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

   /**
    * Insert a new Question (must not exist)
    * @param iquestion
    * @return true if insert succeed, false if query added question
    *         fail (ie. database bug)
    * @throws NamingException
    * @throws SQLException
    */
   @Override
   public boolean insertQuestion( final IQuestion iquestion )
       throws NamingException, SQLException
   {
       Question question = Question.class.cast( iquestion );
       final String sql = "CALL add_question( '"
           + question.getIdLocal()
           + "', '"
           + question.getName()
           + "', '"
           + question.getDescription()
           + "', "
           + question.getOrder()
           + ", "
           + question.getParentIdTopic()
           + ", "
           + question.getMinValue()
           + ", "
           + question.getMaxValue()
           + ", "
           + question.getDefaultValue()
           + " );";

       if( logger.isDebugEnabled() ) {
           logger.debug( "insertQuestion: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           ResultSet rSet = query.executeQuery( sql );

           if( rSet.first() ) {
               question.set( rSet );

               return true;
               }
           else {
               if( logger.isDebugEnabled() ) {
                   logger.debug( "No value in ResultSet: " + rSet );
                   }
               return false;
               }
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }
   }

   /**
    *
    * @param idquestion
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   @Override
   public boolean deleteQuestion( final int idquestion )
       throws NamingException, SQLException
   {
       final String whereClause = " WHERE `idquestion`='"
           + idquestion + "';";
       final String sql1 = "DELETE FROM `questions_local`" + whereClause;
       final String sql2 = "DELETE FROM `questions`" + whereClause;

       if( logger.isDebugEnabled() ) {
            logger.debug( "deleteQuestion: sql1 = " + sql1 );
            logger.debug( "deleteQuestion: sql2 = " + sql2 );
            }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           // TODO: improve (roll back -> move to database)
           /*int x1 =*/ query.doUpdate( sql1 );
           int x2 = query.doUpdate( sql2 );

           if( /*x1 == 1 &*/ x2 == 1 ) {
               return true;
               }
           }
       finally {
           query.quietClose();
           }

       return false;
   }

   /**
    *
    * @param iquestion
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   @Override
   public boolean updateQuestion( final IQuestion iquestion )
       throws NamingException, SQLException
   {
       final String sql = "CALL update_question( "
           + iquestion.getIdQuestion()
           + ", '"
           + iquestion.getIdLocal()
           + "', '"
           + iquestion.getName()
           + "', '"
           + iquestion.getDescription()
           + "', "
           + iquestion.getOrder()
           + ", "
           + iquestion.getParentIdTopic()
           + ", "
           + iquestion.getIsActive()
           + ", "
           + iquestion.getMinValue()
           + ", "
           + iquestion.getMaxValue()
           + ", "
           + iquestion.getDefaultValue()
           + " );";

       if( logger.isDebugEnabled() ) {
           logger.debug( "updateQuestion: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           query.doUpdate( sql );
           }
       finally {
           query.quietClose();

           MySQLDAOFactory.returnConnectionToPool( connection );
           }

       return true;
   }

}
